package com.CommunityHub.kiana.dao;

import com.CommunityHub.kiana.po.HouseInfo;
import com.CommunityHub.kiana.utils.DBUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class HouseDAO {
    public List<HouseInfo> getAllHouses() throws SQLException {
        List<HouseInfo> houses = new ArrayList<>();
        String sql = "SELECT * FROM House_Info";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            while (rs.next()) {
                HouseInfo house = new HouseInfo();
                house.setHouseId(rs.getInt("house_id"));
                house.setHouseName(rs.getString("house_name"));
                house.setBuildingNumber(rs.getString("building_number"));
                house.setUnitNumber(rs.getString("unit_number"));
                house.setRoomNumber(rs.getString("room_number"));
                house.setArea(rs.getBigDecimal("area"));
                house.setPrice(rs.getBigDecimal("price"));
                house.setStatus(rs.getInt("status"));
                house.setBuyerId(rs.getInt("buyer_id"));
                house.setHouseImage(rs.getString("house_image"));
                houses.add(house);
            }
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }

        return houses;
    }

    public boolean addHouse(HouseInfo house) throws SQLException {
        String sql = "INSERT INTO house_info (house_name, building_number, unit_number, room_number, " +
                "area, price, status, buyer_id, house_image) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置参数
            pstmt.setString(1, house.getHouseName());
            pstmt.setString(2, house.getBuildingNumber());
            pstmt.setString(3, house.getUnitNumber());
            pstmt.setString(4, house.getRoomNumber());
            pstmt.setBigDecimal(5, house.getArea());
            pstmt.setBigDecimal(6, house.getPrice());
            pstmt.setInt(7, 0);
            pstmt.setObject(8, null); // buyer_id可为空
            pstmt.setString(9, house.getHouseImage());

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public static boolean updateHouseImage(String buildingNumber,String unitNumber, String roomNumber, String houseImage) throws SQLException {
        String sql = "UPDATE House_Info SET house_image = ? WHERE building_number = ? AND unit_number = ? AND room_number = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置参数

            pstmt.setString(1, houseImage);
            pstmt.setString(2, buildingNumber);
            pstmt.setString(3, unitNumber);
            pstmt.setString(4, roomNumber);

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public boolean updateHouseStatus(int houseId, int status, Integer buyerId, double price) throws SQLException {
        String sql = "UPDATE House_Info SET status = ?, buyer_id = ?, price = ? WHERE house_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, status);
            pstmt.setObject(2, buyerId);
            pstmt.setBigDecimal(3, BigDecimal.valueOf(price));
            pstmt.setInt(4, houseId);

            return pstmt.executeUpdate() > 0;
        } finally {
            if (pstmt != null) pstmt.close();
            if (conn != null) conn.close();
        }
    }

    public int getAllCountHouses() throws SQLException {
        String sql = "SELECT COUNT(*) FROM House_Info";
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        int count = 0;

        try {
            conn = DBUtil.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            if (rs.next()) {
                return rs.getInt(1); // 获取第一列的计数结果
            }

        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }

        return 0;
    }
}
